package org.nokatag.system;

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.nokatag.jdom.JDOMException;
import org.nokatag.password.AESPlus;

/**
 * 数据库类型
 * @author xiefangjian
 */
public class FileUploadDBSQL {
private static Boolean isCreateTable = false;//是否已经建表
//-----------------SQLServer 200数据库----------------------------------
private static final String SQLServer_SQL="create table "+Concest.FILEDBSAVE_TABLE+" ("
	+Concest.UP_FILEID+"               		varchar(200)         not null,"
	+Concest.UP_FILENAME+"            		varchar(200)         null,"
	+Concest.UP_FILECREATEDATE+"      		varchar(50)          null,"
	+Concest.UP_FILETYPE+"             		varchar(200)         null,"
	+Concest.UP_FILEJSPTIMEID+"            	varchar(240)         null,"
	+Concest.UP_FILESELECTTYPE+"          	varchar(200)         null,"
	+Concest.UP_FILEFTEXT+"          		varchar(200)         null,"
	+Concest.UP_FILEDOCUMENT+"             	TEXT                 null,"
	+"constraint PK_"+Concest.FILEDBSAVE_TABLE+" primary key nonclustered ("+Concest.UP_FILEID+")"
	+")";
//-----------------Oracle数据库----------------------------------
private static final String Oracle_SQL="create table "+Concest.FILEDBSAVE_TABLE+"  ("
	+Concest.UP_FILEID+"               		VARCHAR(200)        not null,"
	+Concest.UP_FILENAME+"             		VARCHAR(200),"
	+Concest.UP_FILECREATEDATE+"      		VARCHAR(50),"
	+Concest.UP_FILETYPE+"            		VARCHAR(200),"
	+Concest.UP_FILEJSPTIMEID+"             VARCHAR(240),"
	+Concest.UP_FILESELECTTYPE+"            VARCHAR(200),"
	+Concest.UP_FILEFTEXT+"          		varchar(200)         null,"
	+Concest.UP_FILEDOCUMENT+"              CLOB,"
	+"constraint PK_"+Concest.FILEDBSAVE_TABLE+" primary key ("+Concest.UP_FILEID+")"
	+")";
private static final String MySQL_SQL="create table "+Concest.FILEDBSAVE_TABLE+" ("
	+Concest.UP_FILEID+"               		varchar(200) 		not null,"
	+Concest.UP_FILENAME+"             		varchar(200),"
	+Concest.UP_FILECREATEDATE+"      		varchar(50),"
	+Concest.UP_FILETYPE+"             		varchar(200),"
	+Concest.UP_FILEJSPTIMEID+"             varchar(240),"
	+Concest.UP_FILESELECTTYPE+"           	varchar(200),"
	+Concest.UP_FILEFTEXT+"          		varchar(200)         null,"
	+Concest.UP_FILEDOCUMENT+"              TEXT,"
	+"primary key ("+Concest.UP_FILEID+")"
	+")";
private static final String PGSQL_SQL="create table "+Concest.FILEDBSAVE_TABLE+" ("
	+Concest.UP_FILEID+"               VARCHAR(200)         not null,"
	+Concest.UP_FILENAME+"             VARCHAR(200)         null,"
	+Concest.UP_FILECREATEDATE+"       VARCHAR(50)                 null,"
	+Concest.UP_FILETYPE+"            VARCHAR(200)         null,"
	+Concest.UP_FILEJSPTIMEID+"            VARCHAR(240)         null,"
	+Concest.UP_FILESELECTTYPE+"           VARCHAR(200)         null,"
	+Concest.UP_FILEFTEXT+"          		varchar(200)         null,"
	+Concest.UP_FILEDOCUMENT+"            TEXT           null"
	+")";
private static final String DB2_SQL="create table "+Concest.FILEDBSAVE_TABLE+"  ("
	+Concest.UP_FILEID+"               VARCHAR(200)        not null,"
	+Concest.UP_FILENAME+"             VARCHAR(200),"
	+Concest.UP_FILECREATEDATE+"        VARCHAR(50),"
	+Concest.UP_FILETYPE+"             VARCHAR(200),"
	+Concest.UP_FILEJSPTIMEID+"          VARCHAR(240),"
	+Concest.UP_FILESELECTTYPE+"            VARCHAR(200),"
	+Concest.UP_FILEFTEXT+"          		varchar(200)         null,"
	+Concest.UP_FILEDOCUMENT+"             CLOB(1 G)"
	+")";

private static final String Other_SQL="create table "+Concest.FILEDBSAVE_TABLE+"  ("
+Concest.UP_FILEID+"               VARCHAR(200)        not null,"
+Concest.UP_FILENAME+"             VARCHAR(200),"
+Concest.UP_FILECREATEDATE+"        VARCHAR(50),"
+Concest.UP_FILETYPE+"             VARCHAR(200),"
+Concest.UP_FILEJSPTIMEID+"          VARCHAR(240),"
+Concest.UP_FILESELECTTYPE+"            VARCHAR(200),"
+Concest.UP_FILEFTEXT+"          		varchar(200)         null,"
+Concest.UP_FILEDOCUMENT+"             CLOB(8000)"
+")";
public static Boolean isText(){
	try{
		Connection con = DBCon.getCon();
		DatabaseMetaData dmd = con.getMetaData();
		String DataBaseProductName = dmd.getDatabaseProductName();
		if(DataBaseProductName.equalsIgnoreCase("Microsoft SQL Server") || DataBaseProductName.equalsIgnoreCase("MySQL") || DataBaseProductName.equalsIgnoreCase("PostgreSQL")  || DataBaseProductName.equalsIgnoreCase("Greenplum") ){
			return true;
		}
	}catch(Exception se){
	}
	return false;
}
//-----------------创建数据库-------------------------------------
public static boolean CeeateFileTable(){
	try{
		if(FileUploadDBSQL.isCreateTable){
			return false;
		}
		if(!DBTableISCreateed()){
			Connection con = DBCon.getCon();
			DatabaseMetaData dmd = con.getMetaData();
			Statement sta= con.createStatement();
			String DataBaseProductName = dmd.getDatabaseProductName();
			if(DataBaseProductName.equalsIgnoreCase("Oracle")){
				BugInfoWrint.Print("开始建表："+Oracle_SQL);
				sta.execute(Oracle_SQL);
			}else if(DataBaseProductName.equalsIgnoreCase("Microsoft SQL Server")){
				BugInfoWrint.Print("开始建表："+SQLServer_SQL);
				sta.execute(SQLServer_SQL);
			}else if(DataBaseProductName.equalsIgnoreCase("MySQL")){
				BugInfoWrint.Print("开始建表："+MySQL_SQL);
				sta.execute(MySQL_SQL);
			}else if(DataBaseProductName.equalsIgnoreCase("PostgreSQL")){
				BugInfoWrint.Print("开始建表："+PGSQL_SQL);
				sta.execute(PGSQL_SQL);
			}else if(DataBaseProductName.startsWith("DB2")){
				BugInfoWrint.Print("开始建表："+DB2_SQL);
				sta.execute(DB2_SQL);
			}else if(DataBaseProductName.equalsIgnoreCase("Greenplum")){
				BugInfoWrint.Print("开始建表："+PGSQL_SQL);
				sta.execute(PGSQL_SQL);
			}else{
				BugInfoWrint.Print("开始建表："+Other_SQL);
				sta.execute(Other_SQL);
			}
			if(!con.getAutoCommit()){//非自动提交模式
				con.commit();//提交更改
			}
			sta.close();
			FileUploadDBSQL.isCreateTable=true;
			return true;
		}else{
			BugInfoWrint.Print("数据表已经存在");
			return false;
		}
	}catch(Exception se){
		//BugInfoWrint.Print(se);
		return false;
	}
}
//---------------检测数据表存在----------------------------------
public static boolean DBTableISCreateed(){
	try{
		Connection con = DBCon.getCon();
		Statement sta = null;
		if(con!=null)
			sta = con.createStatement();
		if(sta!=null){
			ResultSet rs = sta.executeQuery("select "+Concest.UP_FILEID+" from "+Concest.FILEDBSAVE_TABLE+" ");
			rs.close();
			sta.close();
			FileUploadDBSQL.isCreateTable=true;
			return true;
		}
	}catch(Exception se){
		return false;
	}
	return false;
}
//------------------------插入文件---------------------------------------
public static boolean saveAsNoka(Map<String,String> map){
	try{
		Connection conn = DBCon.getCon();
		PreparedStatement ps = conn.prepareStatement("insert into "+Concest.FILEDBSAVE_TABLE+"("+Concest.UP_FILEID+","+Concest.UP_FILENAME+","+Concest.UP_FILECREATEDATE+","+Concest.UP_FILETYPE+","+Concest.UP_FILEJSPTIMEID+","+Concest.UP_FILESELECTTYPE+","+Concest.UP_FILEFTEXT+","+Concest.UP_FILEDOCUMENT+") values (?,?,?,?,?,?,?,?)");
		ps.setString(1, map.get(Concest.UP_FILEID));
		ps.setString(2, AESPlus.encrypt(map.get(Concest.UP_FILENAME),Concest.SQLKEY));
		ps.setString(3, map.get(Concest.UP_FILECREATEDATE));
		ps.setString(4, map.get(Concest.UP_FILETYPE));
		ps.setString(5, map.get(Concest.UP_FILEJSPTIMEID));
		ps.setString(6, map.get(Concest.UP_FILESELECTTYPE));
		ps.setString(7, map.get(Concest.UP_FILEFTEXT));
		inputPath(8,ps,map.get(Concest.UP_FILE_PATH));
		if(ps.executeUpdate()>0){
			if(!conn.getAutoCommit()){//非自动提交模式
				conn.commit();//提交更改
			}
			ps.close();
			BugInfoWrint.Print("插入数据成功");
			return true;
		}else{
			BugInfoWrint.Print("插入数据失败");
			return false;
		}
	}catch(Exception se){
		BugInfoWrint.Print(se);
		return false;
	}
}
//-------------删除--------------------------------------------------
public static List<String> delAsNoka(String[] fileid){//返回删除文件的路径
	List<String> fileList = new ArrayList<String>();
	try{
		Connection conn = DBCon.getCon();
		//-------------------组织SQL---------------------------------------------------------------------------------------
		String dsql="DELETE  FROM "+Concest.FILEDBSAVE_TABLE+" WHERE "+Concest.UP_FILEID+" in( ";
		String fsql="SELECT "+Concest.UP_FILEDOCUMENT+"  FROM "+Concest.FILEDBSAVE_TABLE+" WHERE "+Concest.UP_FILEID+" in( ";
		for(int i=0;i<fileid.length;i++){
			dsql+="?,";
			fsql+="?,";
		}
		dsql=dsql.substring(0,dsql.length()-1)+")";
		fsql=fsql.substring(0,fsql.length()-1)+")";
		//------------------查询需要删除的文件地址-----------------------------------------------------------------------------
		PreparedStatement fps = null;
		try{
			fps = conn.prepareStatement(fsql);
			for(int i=0;i<fileid.length;i++){
				fps.setString(i+1,fileid[i]);
			}
			ResultSet frs = fps.executeQuery();
			while(frs.next()){
				fileList.add(frs.getString(Concest.UP_FILEDOCUMENT));
			}
		}catch(Exception se){
			BugInfoWrint.Print(se);
		}finally{
			if(null!=fps){
				fps.close();
			}
		}
		//------------------从数据库中删除-------------------------------------------------------------------------------------
		PreparedStatement dps = null;
		try{
			dps = conn.prepareStatement(dsql);
			for(int i=0;i<fileid.length;i++){
				dps.setString(i+1,fileid[i]);
			}
			if(dps.executeUpdate()>0){
				if(!conn.getAutoCommit()){//非自动提交模式
					conn.commit();//提交更改
				}
			}
		}catch(Exception se){
			BugInfoWrint.Print(se);
		}finally{
			if(null!=dps){
				dps.close();
			}
		}
	}catch(Exception se){
		BugInfoWrint.Print(se);
	}
	return fileList;
}
//-----------读取------------------------------------------------------
public static FileItem ReadFileNoka(String fileid){
	ResultSet rs = null;
	PreparedStatement ps =null;
	try{
		Connection conn = DBCon.getCon();
		ps = conn.prepareStatement("SELECT "
				+Concest.UP_FILEID+","
				+Concest.UP_FILENAME+","
				+Concest.UP_FILECREATEDATE+","
				+Concest.UP_FILETYPE+","
				+Concest.UP_FILEJSPTIMEID+","
				+Concest.UP_FILESELECTTYPE+","
				+Concest.UP_FILEFTEXT+","
				+Concest.UP_FILEDOCUMENT
				+" FROM "+Concest.FILEDBSAVE_TABLE+"  WHERE "+Concest.UP_FILEID+"=?");
		ps.setString(1,fileid);
		rs = ps.executeQuery();
		if (rs.next()) {
			if(isText()){
				
			}
			return new FileItem(
					rs.getString(Concest.UP_FILEID),
					AESPlus.decrypt(rs.getString(Concest.UP_FILENAME),Concest.SQLKEY),
					rs.getString(Concest.UP_FILETYPE),
					rs.getString(Concest.UP_FILEFTEXT),
					rs.getString(Concest.UP_FILECREATEDATE),
					filePath(rs)
					);
		}
		rs.close();
		ps.close();
	}catch(Exception se){
		BugInfoWrint.Print(se);
		return null;
	}finally{
		if(null!=rs){
			try{rs.close();}catch(Exception e){}
		}
		if(null!=ps){
			try{ps.close();}catch(Exception e){}
		}
	}
	return null;
}
/**
 * <p>@Description 获取文件路径</p>
 * <p>author rebin </p>
 * <p>@Time 下午1:29:29</p>
 * <p>xiefangjian@163.com</p>
 * @param rs
 * @return
 */
private static String filePath(ResultSet rs){
	try{
		if(isText()){
			return rs.getString(Concest.UP_FILEDOCUMENT);
		}else{
			BufferedReader br = new BufferedReader(rs.getCharacterStream(Concest.UP_FILEDOCUMENT));
			StringBuffer sb = new StringBuffer();
			String line = null;
			while(null!=(line=br.readLine())){
				sb.append(line);
			}
			return sb.toString();
		}
	}catch(Exception se){
		BugInfoWrint.Print(se);
	}
	return null;
}
/**
 * <p>@Description 插入路径</p>
 * <p>author rebin </p>
 * <p>@Time 下午1:34:12</p>
 * <p>xiefangjian@163.com</p>
 * @param i
 * @param ps
 * @param path
 */
private static void inputPath(int i,PreparedStatement ps,String path){
	try{
		if(isText()){
			ps.setString(i, path);
		}else{
			StringReader reader = new StringReader(path);
			ps.setCharacterStream(i, reader,path.length());
		}
	}catch(Exception se){
		BugInfoWrint.Print(se);
	}
}

public static String fileidToFilename(String fileid,HttpServletRequest request){
	try{
		Connection conn = DBCon.getCon();
		PreparedStatement ps = conn.prepareStatement("SELECT "+Concest.UP_FILENAME+" FROM "+Concest.FILEDBSAVE_TABLE+"  WHERE "+Concest.UP_FILEID+" =?");
		ps.setString(1,fileid);
		ResultSet rs = ps.executeQuery();
		String type = null;
		if (rs.next()) {
			type = AESPlus.decrypt(rs.getString(Concest.UP_FILENAME),Concest.SQLKEY);//AESPlus.encrypt(map.get(Concest.UP_FILENAME),Concest.SQLKEY)
		}
		rs.close();
		ps.close();
		return type;
	}catch(Exception se){
		BugInfoWrint.Print(se);
		return null;
	}
}
//------------------检查文件类型---------------------------------
public static boolean IsType(String fileid,HttpServletRequest request){
	Boolean isread = false;
	try{
		Connection conn = DBCon.getCon();
		PreparedStatement ps = conn.prepareStatement("SELECT "+Concest.UP_FILETYPE+" FROM "+Concest.FILEDBSAVE_TABLE+" WHERE "+Concest.UP_FILEID+"=?");
		ps.setString(1,fileid);
		ResultSet rs = ps.executeQuery();
		String filetype="";
		if (rs.next()) {
			filetype=rs.getString("FILETYPE");
		}
		rs.close();
		ps.close();
		for(int i=0;i<Concest.FILEEXTTYPE.size();i++){
			if(filetype.equalsIgnoreCase(Concest.FILEEXTTYPE.get(i))){
				isread=true;
				break;
			}
		}
		return isread;
	}catch(Exception se){
		BugInfoWrint.Print(se);
		return false;
	}
}

public static String fileidToFileNames(String fid,String fileids,Integer showtype,String s,String e,String basepath,HttpServletRequest request) throws FileNotFoundException, JDOMException, IOException{
	StringBuilder bodystr = new StringBuilder();
	try{
		Connection conn = DBCon.getCon();
		String sql="SELECT "+Concest.UP_FILEID+","
							+Concest.UP_FILENAME+","
							+Concest.UP_FILECREATEDATE+","
							+Concest.UP_FILETYPE+","
							+Concest.UP_FILEJSPTIMEID+","
							+Concest.UP_FILESELECTTYPE+","
							+Concest.UP_FILEFTEXT
							+" FROM "+Concest.FILEDBSAVE_TABLE+" WHERE "+Concest.UP_FILEID+" in( ";
		for(int i=0;i<fileids.split(",").length;i++){
			sql+="?,";
		}
		sql=sql.substring(0,sql.length()-1)+")";
		PreparedStatement ps = conn.prepareStatement(sql);
		for(int i=0;i<fileids.split(",").length;i++){
			ps.setString(i+1,fileids.split(",")[i]);
		}
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			switch(showtype){
			case 1:bodystr.append("<div class=\"nk_ajaxinput_vluediv nk_input_lineheight\"><a target=\"_black\" title=\""+rs.getString(Concest.UP_FILEFTEXT)+"\" href='"+basepath+"/nokatag/nokatag_uploadfile/"+fid+"?do=get&fileid="+rs.getString(Concest.UP_FILEID)+"'>"+AESPlus.decrypt(rs.getString(Concest.UP_FILENAME),Concest.SQLKEY)+"</a></div>\n");
			break;
			case 2:bodystr.append("<div class=\"nk_ajaxinput_vluediv nk_input_lineheight\"><a target=\"_black\" title=\""+rs.getString(Concest.UP_FILEFTEXT)+"\" href='"+basepath+"/nokatag/nokatag_uploadfile/"+fid+"?do=red&fileid="+rs.getString(Concest.UP_FILEID)+"'>"+AESPlus.decrypt(rs.getString(Concest.UP_FILENAME),Concest.SQLKEY)+"</a></div>\n");
			break;
			case 0:bodystr.append(s+AESPlus.decrypt(rs.getString(Concest.UP_FILENAME),Concest.SQLKEY)+e+"\n");
			break;
			case 3:bodystr.append(basepath+"/nokatag/nokatag_uploadfile/"+fid+"?do=red&fileid="+rs.getString(Concest.UP_FILEID)+"&ft="+rs.getString(Concest.UP_FILEFTEXT)+"\n");
			break;
			}
		}
		rs.close();
		ps.close();
		}catch(Exception se){
			se.printStackTrace();
		}
	return bodystr.toString();
}

public static List<Map<String,String>> fileHead(String path,String filetype,String type,String fvalue,String sessionid,HttpServletRequest request){
	try{
		filetype=filetype.replace("*.", "").replace(";", ",");
		Connection conn = DBCon.getCon();
		String uid = null;
		String sid = null;
		String pid = null;
		Integer idx = 1;
		if(sessionid!=null){
			uid = "null".equals(sessionid.split(",")[0])?null:sessionid.split(",")[0];//用户自定义id
			sid = sessionid.split(",")[1];//sessionid
			pid = sessionid.split(",")[2];//页面id
		}
		String sqls="SELECT "
						+Concest.UP_FILEID+","
						+Concest.UP_FILENAME+","
						+Concest.UP_FILECREATEDATE+","
						+Concest.UP_FILETYPE+","
						+Concest.UP_FILEJSPTIMEID+","
						+Concest.UP_FILESELECTTYPE+","
						+Concest.UP_FILEFTEXT
				+" FROM "+Concest.FILEDBSAVE_TABLE+" WHERE "+Concest.UP_FILETYPE+" in( ";
		for(int i=0;i<filetype.split(",").length;i++){
			sqls+="?,";
		}
		sqls=sqls.substring(0,sqls.length()-1)+")";
		if("uid".equalsIgnoreCase(type)){
			sqls+=" AND "+Concest.UP_FILEJSPTIMEID+" LIKE ? ";
		}else if("sid".equalsIgnoreCase(type)){
			sqls+=" AND "+Concest.UP_FILEJSPTIMEID+" LIKE ? ";
		}else if("pid".equalsIgnoreCase(type)){
			sqls+=" AND "+Concest.UP_FILEJSPTIMEID+" LIKE ? ";
		}
		if(null!=fvalue && fvalue.trim().length()>0){
			sqls+=" OR "+Concest.UP_FILEID+" in(";
			for(int i=0;i<fvalue.split(",").length;i++){
				sqls+="?,";
			}
			sqls=sqls.substring(0,sqls.length()-1)+")";
		}
		if(null==conn){
			return null;
		}
		PreparedStatement ps = conn.prepareStatement(sqls);
		for(int i=0;i<filetype.split(",").length;i++){
			ps.setString(idx++,filetype.split(",")[i]);
		}
		if("uid".equalsIgnoreCase(type)){
			ps.setString(idx++,uid+",%");
		}else if("sid".equalsIgnoreCase(type)){
			ps.setString(idx++,"%,"+sid+",%");
		}else if("pid".equalsIgnoreCase(type)){
			ps.setString(idx++,"%,"+pid);
		}
		if(null!=fvalue && fvalue.trim().length()>0){
			for(String v:fvalue.split(",")){
				ps.setString(idx++,v);
			}
		}
		ResultSet rs = ps.executeQuery();
		List<Map<String,String>> FHList=new ArrayList<Map<String,String>>();
		while(rs.next()){
			Map<String,String> fMap=new HashMap<String,String>();
			fMap.put(Concest.UP_FILEID, rs.getString(Concest.UP_FILEID));//上传的文件id
			fMap.put(Concest.UP_FILENAME, AESPlus.decrypt(rs.getString(Concest.UP_FILENAME),Concest.SQLKEY));//上传的文件名
			fMap.put(Concest.UP_FILECREATEDATE, rs.getString(Concest.UP_FILECREATEDATE));//上传文件的创建时间
			//fMap.put(Concest.UP_FILEPATH, rs.getString(Concest.UP_FILEPATH));//文件路径
			fMap.put(Concest.UP_FILETYPE, rs.getString(Concest.UP_FILETYPE));//文件类型
			fMap.put(Concest.UP_FILEJSPTIMEID, rs.getString(Concest.UP_FILEJSPTIMEID));//过滤
			fMap.put(Concest.UP_FILESELECTTYPE, rs.getString(Concest.UP_FILESELECTTYPE));//过滤类型
			fMap.put(Concest.UP_FILEFTEXT, rs.getString(Concest.UP_FILEFTEXT));//备注
			FHList.add(fMap);
		}
		rs.close();
		ps.close();
		return FHList;
	}catch(Exception se){
		return null;
	}
}

/**
 * 文件输出类型
 * @param name
 * @return
 */
public static String getContentType(String names) {
	String name = names.toLowerCase();//转小写
	if(!name.startsWith(".")){
		name ="."+name;
	}
    if (name.endsWith(".js")) {
        return "text/javascript";
    } else if (name.endsWith(".css")) {
        return "text/css";
    } else if (name.endsWith(".html")) {
        return "text/html";
    } else if (name.endsWith(".htm")) {
        return "text/html";
    }else if (name.endsWith(".txt")) {
        return "text/plain";
    } else if (name.endsWith(".gif")) {
        return "image/gif";
    } else if (name.endsWith(".jpg") || name.endsWith(".jpeg")) {
        return "image/jpeg";
    } else if (name.endsWith(".png")) {
        return "image/png";
    } else {
        return null;
    }
}

}
